from lib.sqlite_api import DBApi
from lib.log import DBG, INFO, ERROR, EXCEPTION

class DBApiExt(object):

    @classmethod
    def new_dbapi(cls, db):
        return DBApi(db=db)

    @classmethod
    def close_dbapi(cls, dbapi):
        dbapi.close()

    @classmethod
    def create_table(cls, dbapi, table_name, table_struct):
        sql = f"CREATE TABLE IF NOT EXISTS {table_name} ({table_struct})"
        dbapi.exec_sql(sql)

    # @classmethod
    # def insert_record(cls, dbapi, table_name, tuple_value):
    #     # cur.execute("INSERT INTO test values(?,?,?)", (6, "zgq", 20))
    #     if type(tuple_value) != tuple:
    #         print("参数错误:{tuple_value}")
    #         return None
    #     tuple_len = len(tuple_value)
    #     if tuple_len == 0:
    #         print("参数错误:{tuple_value}")
    #         return None
    #
    #     format_str = f"INSERT INTO {table_name} values({','.join(['?'] * tuple_len)})"
    #     return dbapi.exec_record_format(format_str, tuple_value)

    @classmethod
    def insert_record_list(cls, dbapi, table_name, tuple_value_list):
        # cur.execute("REPLACE OR INSERT INTO test values(?,?,?)", [(6, "zgq", 20)])
        if type(tuple_value_list) != list or len(tuple_value_list) == 0:
            print(f"参数错误:{tuple_value_list}")
            return None

        first_value = tuple_value_list[0]
        if type(first_value) != tuple:
            print(f"参数错误:{tuple_value_list}")
            return None
        tuple_len = len(first_value)

        format_str = f"REPLACE INTO {table_name} values({','.join(['?'] * tuple_len)})"
        return dbapi.exec_many_records_format(format_str, tuple_value_list)

    # @classmethod
    # def update_record(cls, dbapi, table_name, update_value, where_value):
    #     # cur.execute("UPDATE test SET name='haha' WHERE id=3")
    #
    #     sql = f"UPDATE {table_name} SET {update_value} WHERE {where_value}"
    #     return dbapi.exec_sql(sql)

    @classmethod
    def delete_record(cls, dbapi, table_name, where_value):
        # cur.execute("DELETE FROM test WHERE id=3")
        sql = f"DELETE FROM {table_name} WHERE {where_value}"
        return dbapi.exec_sql(sql)

    @classmethod
    def insert_update_record(cls, dbapi, table_name):
        pass

    @classmethod
    def search_record(cls, dbapi, table_name, select_value=None, size=None, where=None, order=None):
        # cur.execute("SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY'")
        if select_value == None:
            select_value = "*"

        if where is None:
            where_str = ""
        else:
            where_str = f"WHERE {where}"

        if order is None:
            order = ""

        sql = f"select {select_value} from {table_name} {where_str} {order}"
        print(sql)
        cur = dbapi.exec_sql(sql)
        if size is None:
            return cur.fetchall()
        else:
            return cur.fetchmany(size)

    @classmethod
    def count_record(cls, dbapi, table_name, where: str=None):
        # cur.execute("DELETE FROM test WHERE id=3")
        return dbapi.exec_count(table_name=table_name, where=where)

    @classmethod
    def _gen_update_sql(cls, table_name, update_data: dict, where_data: dict):
        """
        生成插入SQL语句

        UPDATE users SET name = 'John', age = 30 WHERE id = 1;

        :param where_data: 过滤条件
        :param update_data: 更新数据
        :param data: 字典/数组，包含要插入的字段和对应的值
        :return: 插入SQL语句
        """
        # 构造插入SQL语句

        where_set_str = ""
        value_set_str = ""

        is_first = True
        for key in update_data:
            value = update_data[key]

            if is_first:
                if type(value) == str:
                    value_set_str += f"{key}='{value}'"
                else:
                    value_set_str += f"{key}={value}"
            else:
                if type(value) == str:
                    value_set_str += f", {key}='{value}'"
                else:
                    value_set_str += f", {key}={value}"

            is_first = False

        is_first = True
        for key in where_data:
            value = where_data[key]

            if is_first:
                if type(value) == str:
                    where_set_str += f"{key}='{value}'"
                else:
                    where_set_str += f"{key}={value}"
            else:
                if type(value) == str:
                    where_set_str += f" AND {key}='{value}'"
                else:
                    where_set_str += f" AND {key}={value}"

            is_first = False

        sql = f"UPDATE {table_name} SET {value_set_str} WHERE {where_set_str}"

        return sql

    @classmethod
    def _gen_insert_sql(cls, table_name, ins_datas: list):
        """
        生成插入SQL语句

        INSERT INTO users (name, age, email)
        VALUES
        ('John', 25, 'john@example.com'),
        ('Jane', 30, 'jane@example.com'),
        ('Bob', 35, 'bob@example.com');

        :param ins_datas: 对象数组，每一项key一样
        :return: 插入SQL语句
        """
        # 构造插入SQL语句

        try:
            if type(ins_datas) == 0 or len(ins_datas) == 0:
                ERROR(f"生成sql时，参数错误: {ins_datas}")
                return None

            key_list = list(ins_datas[0].keys())
            key_list_str = ",".join(key_list)
            sql = f"INSERT INTO {table_name} ({key_list_str}) VALUES "

            is_first = True
            for ins_data in ins_datas:
                if is_first:
                    sql += "("
                else:
                    sql += ", ("

                for i in range(len(key_list)):
                    key = key_list[i]
                    value = ins_data[key]

                    if i == 0:
                        if type(value) == str:
                            sql += f"'{value}'"
                        else:
                            sql += f"{value}"
                    else:
                        if type(value) == str:
                            sql += f",'{value}'"
                        else:
                            sql += f",{value}"

                sql += ")"

                is_first = False

            return sql
        except Exception as exc:
            ERROR(f"构建sql异常: {exc}")
            return None

    @classmethod
    def _gen_exist_sql(cls, table_name, where_data: dict):
        """
        生成插入SQL语句

        UPDATE users SET name = 'John', age = 30 WHERE id = 1;

        :param where_data: 过滤条件
        :return: 插入SQL语句
        """
        # 构造插入SQL语句

        where_set_str = ""

        is_first = True
        for key in where_data:
            value = where_data[key]

            if is_first:
                if type(value) == str:
                    where_set_str += f"{key}='{value}'"
                else:
                    where_set_str += f"{key}={value}"

                is_first = False
            else:
                if type(value) == str:
                    where_set_str += f" AND {key}='{value}'"
                else:
                    where_set_str += f" AND {key}={value}"

        sql = f"SELECT EXISTS(SELECT 1 FROM {table_name} WHERE {where_set_str})"

        return sql

    @classmethod
    def insert_record(cls, dbapi, table_name, ins_datas: list):
        try:
            sql = cls._gen_insert_sql(table_name=table_name, ins_datas=ins_datas)
            if sql:
                result = dbapi.exec_sql(sql)
            else:
                result = None
        except Exception as exc:
            ERROR(f"构建sql异常: {exc}, {ins_datas}")
            return None

        return result

    @classmethod
    def update_record(cls, dbapi, table_name, update_data: dict, where_data: dict):
        sql = cls._gen_update_sql(table_name=table_name, update_data=update_data, where_data=where_data)
        if sql:
            result = dbapi.exec_sql(sql)
        else:
            result = None

        return result

    @classmethod
    def exist_record(cls, dbapi, table_name, where_data: dict):
        sql = cls._gen_exist_sql(table_name=table_name, where_data=where_data)
        if sql:
            result = dbapi.exec_exist(sql=sql)
        else:
            result = None

        return result


class DBApiExtTest(object):
    DB = "qyhhs.db"
    # 资源分类
    RES_TYPE_VIDEO = "video"
    RES_TYPE_IMAGE = "image"
    RES_TYPE_TEXT = "text"
    RES_TYPE_bt = "bt"

    # 网站分类表
    TABLE_CLASS_NAME = "class"
    TABLE_CLASS_STRUCT = """
        id INTEGER PRIMARY KEY, 
        name TEXT NOT NULL, 
        res_type TEXT NOT NULL,
        url TEXT NOT NULL,
        page_count INTEGER,
        res_count INTEGER
        """

    # 资源表
    TABLE_RESOURCE_NAME = "resource"
    TABLE_RESOURCE_STRUCT = f"""
        id INTEGER NOT NULL, 
        class_id INTEGER NOT NULL, 
        name TEXT NOT NULL,
        url TEXT,
        res_type TEXT,
        head_image TEXT,
        text TEXT,
        images TEXT,
        video TEXT,
        label1 TEXT,
        label2 TEXT,
        label3 TEXT,
        finish INTEGER,
        PRIMARY KEY (id, class_id)
        foreign key(class_id) references class (id)
        """

    def __init__(self):
        self.dbapi = DBApiExt.new_dbapi(db=self.DB)
        DBApiExt.create_table(self.dbapi, self.TABLE_CLASS_NAME, self.TABLE_CLASS_STRUCT)
        DBApiExt.create_table(self.dbapi, self.TABLE_RESOURCE_NAME, self.TABLE_RESOURCE_STRUCT)
        #
        # data = {"id": 22222, "url": "xxxx", "page_count": 7, "name": "xxxx", "res_type": "xxx"}
        # sql = self.generate_insert_sql2(data=data, table_name=self.TABLE_CLASS_NAME)
        sql = "INSERT INTO class (id,url,name,res_type,page_count,res_count) VALUES (20,'https://www.qyhhs2.digital/cn/home/web/index.php/vod/type/id/20.html','国产精品','video',0,0), (21,'https://www.qyhhs2.digital/cn/home/web/index.php/vod/type/id/21.html','主播大秀','video',0,0), (22,'https://www.qyhhs2.digital/cn/home/web/index.php/vod/type/id/22.html','唯美视频','video',0,0), (23,'https://www.qyhhs2.digital/cn/home/web/index.php/vod/type/id/23.html','口交视频','video',0,0), (24,'https://www.qyhhs2.digital/cn/home/web/index.php/vod/type/id/24.html','日本有碼','video',0,0), (25,'https://www.qyhhs2.digital/cn/home/web/index.php/vod/type/id/25.html','日本無碼','video',0,0), (26,'https://www.qyhhs2.digital/cn/home/web/index.php/vod/type/id/26.html','动漫视频','video',0,0), (27,'https://www.qyhhs2.digital/cn/home/web/index.php/vod/type/id/27.html','欧美视频','video',0,0)"
        self.dbapi.exec_sql(sql)

    def generate_insert_sql(self, data, table_name):
        """
        生成插入SQL语句
        :param data: 字典，包含要插入的字段和对应的值
        :return: 插入SQL语句
        """
        # 构造插入SQL语句
        keys = ", ".join(data.keys())
        values = ", ".join(["%s"] * len(data))
        sql = f"INSERT INTO {table_name} ({keys}) VALUES ({values})"
        return sql

    def generate_insert_sql2(self, data: dict, table_name):
        """
        生成插入SQL语句
        :param data: 字典，包含要插入的字段和对应的值
        :return: 插入SQL语句
        """
        # 构造插入SQL语句

        key_list = []
        value_list = []

        for key in data:
            key_list.append(key)
            value_list.append(data[key])

        key_list_str = ""
        value_list_str = ""
        for i in range(len(value_list)):
            if i == 0:
                if type(value_list[i]) == str:
                    value_list_str += f"'{value_list[i]}'"
                else:
                    value_list_str += f"{value_list[i]}"

                key_list_str += key_list[i]
            else:
                if type(value_list[i]) == str:
                    value_list_str += f",'{value_list[i]}'"
                else:
                    value_list_str += f",{value_list[i]}"

                key_list_str += f",{key_list[i]}"

        sql = f"INSERT INTO {table_name} ({key_list_str}) VALUES ({value_list_str})"

        # sql = f"INSERT INTO {table_name} ({','.join(key_list)})  ({','.join(value_list)})"
        #
        # sql = f"INSERT INTO {table_name} ({','.join(key_list)}) VALUES ({','.join(value_list)})"
        return sql

    def test_generate_insert_sql(self):
        data = {"id": 22222, "url": "xxxx", "page_count": 7, "name": "xxxx", "res_type": "xxx"}
        sql = self.generate_insert_sql2(data=data, table_name=self.TABLE_CLASS_NAME)


if __name__ == '__main__':
    my_tuple = (1, 2, 3, "hello", 5.5)
    print(f"====={my_tuple}======")

    test = DBApiExtTest()
